﻿CREATE PROCEDURE s_NextStatesAllLevels AS
DECLARE @GroupName sysname SET @GroupName = 'Bankruptcy'
DECLARE @StartID int SET @StartID = 255--226
DECLARE @Sign int SET @Sign = -1;
WITH Steps AS(
	SELECT @StartID StateFromID,StateToID,NameTo,HasAccess,Direction,Dir,StateToPos,StateFlowPosTo,0 AS Level
	FROM StateFlowToByGroup(@GroupName,@StartID)WHERE SIGN(Direction) = SIGN(@SIGN)-- AND HasAccess = 1
UNION ALL
	SELECT S.StateToID,SF.StateToID,SF.NameTo,SF.HasAccess,SF.Direction,SF.Dir,SF.StateToPos,SF.StateFlowPosTo,S.Level+1
	FROM Steps S CROSS APPLY StateFlowToByGroup(@GroupName,S.StateToID)SF
	WHERE  SIGN(S.Direction) = SIGN(SF.Direction) --AND SF.HasAccess = 1
)
SELECT * FROM Steps